package views;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import model.SystemConfiguration;
import nnsom.CallsSOM;
import nnsom.DataGenerator;
import nnsom.FeedRecord;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import com.google.gson.Gson;

import utils.Chronograph;

public class SubscribersNNManagerApp {
	static DateTimeFormatter dateTimeFormatter = DateTimeFormat.forPattern(
			"ddMMYYYYHHmmss").withOffsetParsed();
	static DateTimeFormatter hDateTimeFormatter = DateTimeFormat.forPattern(
			"dd/MM/YYYY HH:mm:ss").withOffsetParsed();

	static DateTime start_time;
	static DateTime end_time;
	static SystemConfiguration systemConfiguration;
	static String SME = "Voz";

	// static int CALLS_LOWER_LIMIT = 50;
	// static String connectionString = "jdbc:mysql://localhost/cdr_db?"
	// + "user=root&password=";

	public static void main(String[] args) {
		Gson gson = new Gson();
		BufferedReader br;
		try {
			br = new BufferedReader(new FileReader("parameters.json"));
			systemConfiguration = gson.fromJson(br, SystemConfiguration.class);
			System.out.println(systemConfiguration.toString());
			String connectionString = String.format(
					"jdbc:mysql://%s:%d/%s?user=%s&password=%s",
					systemConfiguration.getRdbms().getIp(), systemConfiguration
							.getRdbms().getPort(), systemConfiguration
							.getDbName(), systemConfiguration.getRdbms()
							.getUser(), systemConfiguration.getRdbms()
							.getPassword());
			// System.out.println(connectionString);
			SubscribersNNManagerApp app = new SubscribersNNManagerApp();
			app.initDB(connectionString);
			if (args.length == 4) {
				String start_time_s = args[2];
				String end_time_s = args[3];
				start_time = DateTime.parse(start_time_s, dateTimeFormatter);
				end_time = DateTime.parse(end_time_s, dateTimeFormatter);
				String type = args[1];
				System.out.printf("Examining Period: %s - %s\n",
						hDateTimeFormatter.print(start_time),
						hDateTimeFormatter.print(end_time));
				if (type.equalsIgnoreCase("-t")) {
					app.trainNNs();
				} else if (type.equalsIgnoreCase("-d")) {
					app.detectFraud();
				}
			} else {
				app.menu();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
	}

	private Connection connect = null;
	private ResultSet resultSet = null;
	private PreparedStatement pstmtDelSOM = null, pstmtLoadNN = null,
			pstmtInsNN = null, pstmtUpdateNN = null,
			pstmtGetSubscribers = null, pstmtGetSubscribersLimit = null;
	int numberOfRecordsUsed;
	String eg_contents;
	String input_low_values;
	String input_high_values;
	String frequencies_values_s;

	private void initDB(String connectionString) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection(connectionString);
			pstmtDelSOM = connect.prepareStatement("delete from nnsom");
			pstmtLoadNN = connect
					.prepareStatement("SELECT TRAIN_RECORDS, SOM_VALUES, INPUT_LOW_VALUES, INPUT_HIGH_VALUES, OUTPUT_FREQUENCIES "
							+ "FROM nnsom " + "WHERE SUBSCRIBER_ID=?");
			pstmtInsNN = connect
					.prepareStatement("INSERT INTO nnsom("
							+ "SUBSCRIBER_ID, SOM_VALUES, TRAIN_RECORDS, INPUT_LOW_VALUES, INPUT_HIGH_VALUES, OUTPUT_FREQUENCIES) "
							+ "VALUES(?, ?, ?, ?, ?, ?)");
			pstmtUpdateNN = connect
					.prepareStatement("UPDATE nnsom SET SOM_VALUES=?, TRAIN_RECORDS=TRAIN_RECORDS + ? WHERE SUBSCRIBER_ID= ?");
			pstmtGetSubscribers = connect
					.prepareStatement("select user_id, count(dst_id) as callsno from users, calls "
							+ "where users.user_id=calls.src_id and "
							+ "from_unixtime(calls.timestamp) >= ? and from_unixtime(calls.timestamp) <= ? "
							+ "group by user_id");
			// pstmtGetSubscribersLimit = connect
			// .prepareStatement("select user_id, count(dst_id) as callsno from users, calls "
			// + "where users.user_id=calls.src_id and "
			// +
			// "from_unixtime(calls.timestamp) >= ? and from_unixtime(calls.timestamp) <= ? "
			// + "group by user_id having callsno >= ?");
			pstmtGetSubscribersLimit = connect
					.prepareStatement("select src_id, count(dst_id) as callsno from calls where from_unixtime(calls.timestamp) >= ? and from_unixtime(calls.timestamp) <= ? and provider=? group by src_id having callsno >= ?");

			System.out.println("DB initialization succeeded");

		} catch (Exception e) {
			System.err
					.println("Error while establishing connection with connection string "
							+ connectionString);
			e.printStackTrace();
			close();
		} finally {
		}
	}

	public void menu() {
		System.out
				.println("Usage: java -jar NNFraudDetectionComponent_fat.jar -t 01042010000000 31072010120000");
		System.out
				.println("For training using database data between 01/04/2010 00:00:00 and 31/07/2010 12:00:00");
		System.out.println();
		System.out
				.println("Usage: java -jar NNFraudDetectionComponent_fat.jar -d 31072010120000 31072010130000");
		System.out
				.println("For fraud detection using database data between 31/07/2010 12:00:00 and 31/07/2010 13:00:00");
		System.out
				.println("######################################################");
		System.out.println("Demo Version 0.93 (28-12-2011)");
		System.out
				.printf("1. Drop (if exist) and create table and stored procedure in database cdr_db\n");
		System.out
				.printf("2. Generate SOM Neural Networks based on past calls for Telio\n");
		System.out.printf("3. Detect fraud using data of an hour for Telio\n");
		System.out
				.printf("4. Train SOM for subscriber 0034515621702 from 01/01/2011 00:00:00 to 20/03/2011 23:59:59\n");
		System.out
				.printf("5. Detect fraud for subscriber 0034515621702 from 21/03/2011 to 27/03/2011 (NORMAL BEHAVIOR)\n");
		System.out
				.printf("6. Detect fraud for subscriber 0034515621702 in 28/03/2011 (ALERT)\n");
		System.out.print("Choice: ");
		Scanner in = new Scanner(System.in);
		int choice = in.nextInt();
		Chronograph chronos = new Chronograph();
		if (choice == 1) {
			// deleteAllSOMs();
			dropAndCreateNNSOMTable();
		} else if (choice == 2) {
			SME = "Telio";
			trainTelio();
		} else if (choice == 3) {
			SME = "Telio";
			detectTelio();
		} else if (choice == 4) {
			SME = "Voz";
			trainVozFraudster2();
		} else if (choice == 5) {
			SME = "Voz";
			detectVozFraudster2NormalBehavior();
		} else if (choice == 6) {
			SME = "Voz";
			detectVozFraudster2Alert();
		}
		chronos.lap();
		System.out.println("Time elapsed: " + chronos.getElapsedTimeAsString());
	}

	// ################## Telio ####################
	private void trainTelio() {
		start_time = DateTime.parse("01042010000000", dateTimeFormatter);
		end_time = DateTime.parse("31072010120000", dateTimeFormatter);
		System.out.printf("Examining Period: %s - %s\n",
				hDateTimeFormatter.print(start_time),
				hDateTimeFormatter.print(end_time));
		trainNNs();
	}

	private void detectTelio() {
		start_time = DateTime.parse("31072010120000", dateTimeFormatter);
		end_time = DateTime.parse("31072010130000", dateTimeFormatter);
		System.out.printf("Examining Period: %s - %s\n",
				hDateTimeFormatter.print(start_time),
				hDateTimeFormatter.print(end_time));
		detectFraud();
	}

	// ################## Voz #######################
	// fraudster 2 (0034515621702) placed 168 calls in 28/3/2011 from which 55
	// are fraud calls (calls to unusual destinations Congo, Andorra, Egypt)
	private void trainVozFraudster2() {
		start_time = DateTime.parse("01012011000000", dateTimeFormatter);
		end_time = DateTime.parse("20032011235959", dateTimeFormatter);
		System.out.printf("Examining Period: %s - %s\n",
				hDateTimeFormatter.print(start_time),
				hDateTimeFormatter.print(end_time));
		trainNN("0034515621702");
	}

	// testing from 21/03/2011 to 27/03/2011
	private void detectVozFraudster2NormalBehavior() {
		for (int x = 21; x <= 27; x++) {
			String st = String.format("%d032011000000", x);
			String et = String.format("%d032011235959", x);
			start_time = DateTime.parse(st, dateTimeFormatter);
			end_time = DateTime.parse(et, dateTimeFormatter);
			System.out.printf("Examining Period: %s - %s\n",
					hDateTimeFormatter.print(start_time),
					hDateTimeFormatter.print(end_time));
			detectFraud("0034515621702");
			System.out.println("#############################################");
		}
	}

	private void detectVozFraudster2Alert() {
		start_time = DateTime.parse("28032011000000", dateTimeFormatter);
		end_time = DateTime.parse("28032011235959", dateTimeFormatter);
		System.out.printf("Examining Period: %s - %s\n",
				hDateTimeFormatter.print(start_time),
				hDateTimeFormatter.print(end_time));
		detectFraud("0034515621702");
	}

	// ####################################################

	private void dropAndCreateNNSOMTable() {
		String sqlString1 = "DROP TABLE IF EXISTS nnsom; ";
		String sqlString2 = "CREATE TABLE  nnsom (subscriber_id varchar(20) NOT NULL, "
				+ "som_values text NOT NULL,"
				+ "train_records int(10) unsigned NOT NULL DEFAULT 0, "
				+ "input_low_values text NOT NULL, "
				+ "input_high_values text NOT NULL,"
				+ "output_frequencies text NOT NULL,  PRIMARY KEY (subscriber_id)) "
				+ "ENGINE=InnoDB DEFAULT CHARSET=latin1;";
		String sqlString3 = "drop procedure if exists batch_selects; ";
		String sqlString4 = "CREATE PROCEDURE batch_selects(IN sub_id VARCHAR(15), IN timestamp_start LONG, IN timestamp_end LONG, IN period_length_minutes INT) \n"
				+ "BEGIN \n\n"
				+ "DECLARE t LONG; \n"
				+ "SET t = timestamp_start; \n"
				+ "WHILE t < timestamp_end DO \n"
				+ "select  count(*)  as nbr_calls, avg(duration) as avg_duration, sum(is_international) as is_international, "
				+ "sum(is_premium) as is_premium , sum(is_free) as is_free, sum(is_fixed) as is_fixed, sum(is_hidden) as is_hidden, "
				+ "sum(on_weekend) as on_weekend, t as from_time, t + period_length_minutes * 60 as to_time "
				+ "from calls where src_id = sub_id and timestamp >= t and timestamp <= t + period_length_minutes * 60;\n"
				+ "SET t = t + period_length_minutes * 60; \n"
				+ "END WHILE; \n" + "END ";
		// System.out.println(sqlString4);
		try {
			Statement st = connect.createStatement();
			st.executeUpdate(sqlString1);
			st = connect.createStatement();

			st.executeUpdate(sqlString2);
			st = connect.createStatement();

			st.executeUpdate(sqlString3);
			st = connect.createStatement();

			st.executeUpdate(sqlString4);
			System.out.println("Table nnsom created successfully!");
		} catch (SQLException s) {
			System.out.println("Problem in creation of table nnsom "
					+ s.toString());
		}

	}

	private void deleteAllSOMs() {
		try {
			pstmtDelSOM.execute();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		} finally {
		}
		System.out.println("Records deleted");
	}

	public void detectFraud() {
		List<String> subscribers = getSubscribersWithCallData();
		int s = subscribers.size();
		int i = 0;
		for (String subscriber_id : subscribers) {
			i++;
			System.out
					.printf("Subscriber [%d of %d] %s\n", i, s, subscriber_id);
			testFraud(subscriber_id, systemConfiguration
					.getDetectionParameters().getAlertThreshold());
			System.out.println();
		}
	}

	public void detectFraud(String subscriber_id) {
		System.out.printf("Subscriber %s\n", subscriber_id);
		testFraud(subscriber_id, systemConfiguration.getDetectionParameters()
				.getAlertThreshold());
		System.out.println();
	}

	private void testFraud(String subscriber_id, double threshold) {
		// System.out.println("Testing fraud for " + subscriber_id);
		boolean exists = loadNNFromDb(subscriber_id);
		if (!exists) {
			System.out.printf("No historic data for subscriber %s\n",
					subscriber_id);
		} else {
			// System.out.printf("Load NN from database for %s\n",
			// subscriber_id);
			try {
				FileWriter outFile = new FileWriter("temp.eg");
				PrintWriter out = new PrintWriter(outFile);
				out.print(eg_contents);
				out.close();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			int[] freqs = convertFreqsStringToArray(frequencies_values_s);
			CallsSOM som = new CallsSOM("temp.eg", freqs);
			DataGenerator dg = new DataGenerator(connect, subscriber_id,
					start_time.toGregorianCalendar(),
					end_time.toGregorianCalendar());
			// List<FeedRecord> data = dg.generateFeedRecordsStoredProcedure();
			List<FeedRecord> data = dg.generateFeedRecordsSqlQueries();
			int sum = 0;
			for (FeedRecord fr : data) {
				sum += fr.nbr_calls;
			}
			System.out.printf("Existing calls: %d new calls: %d\n",
					numberOfRecordsUsed, sum);
			som.setFeedRecords(data);
			som.testFraud(input_low_values, input_high_values, threshold);
		}
	}

	private int[] convertFreqsStringToArray(String frequencies_values_s) {
		String[] s = frequencies_values_s.split(",");
		int[] freqs = new int[s.length];
		for (int i = 0; i < freqs.length; i++) {
			freqs[i] = Integer.parseInt(s[i]);
		}
		return freqs;
	}

	public void trainNNs() {
		List<String> subscribers = getSubscribersWithCallData(systemConfiguration
				.getTrainingParameters().getMinimumNumberOfCalls());
		int s = subscribers.size();
		int i = 0;
		Chronograph chronos = new Chronograph();
		for (String subscriber_id : subscribers) {
			i++;
			System.out.printf("%d of %d. Train NN for %s", i, s, subscriber_id);
			generateNN(subscriber_id);
			chronos.lap();
			System.out.printf("Time elapsed: %.2f sec\n",
					chronos.getElapsedTimeInMilliseconds() / 1000.0);
			System.out.println();
			chronos.reset();
		}
	}

	public void trainNN(String subscriber_id) {
		Chronograph chronos = new Chronograph();
		System.out.printf("Train NN for %s", subscriber_id);
		generateNN(subscriber_id);
		chronos.lap();
		System.out.printf("Time elapsed: %.2f sec\n",
				chronos.getElapsedTimeInMilliseconds() / 1000.0);
		System.out.println();
		chronos.reset();
	}

	private void generateNN(String subscriber_id) {
		DataGenerator tdg = new DataGenerator(connect, subscriber_id,
				start_time.toGregorianCalendar(),
				end_time.toGregorianCalendar());
		List<FeedRecord> trainData = tdg
				.generateFeedRecordsStoredProcedure(systemConfiguration
						.getTrainingParameters()
						.getTrainingPeriodStepInMinutes());
		// for (FeedRecord fr : trainData) {
		// System.out.println(fr.toString());
		// }
		System.out.printf("\nUsing %d records", trainData.size());
		CallsSOM som;
		boolean existing_subscriber = loadNNFromDb(subscriber_id);
		if (existing_subscriber) {
			// existing subscriber
			try {
				FileWriter outFile = new FileWriter("temp.eg");
				PrintWriter out = new PrintWriter(outFile);
				out.print(eg_contents);
				out.close();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			som = new CallsSOM("temp.eg", trainData);
		} else {
			// new subscriber
			som = new CallsSOM(trainData);
		}
		som.train(false, systemConfiguration.getSomLearningRate());
		som.persistSOM("temp.eg");
		String NL = System.getProperty("line.separator");
		Scanner scanner = null;
		StringBuilder som_values_as_text = new StringBuilder();
		try {
			scanner = new Scanner(new FileInputStream("temp.eg"));
			while (scanner.hasNextLine()) {
				som_values_as_text.append(scanner.nextLine() + NL);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			scanner.close();
		}
		if (existing_subscriber) {
			updateNnIntoDb(subscriber_id, som_values_as_text.toString(),
					trainData.size());
		} else {
			String ilvs = computeLowerValues(trainData);
			String ihvs = computeHigherValues(trainData);
			String ofvs = convertOutputFrequencyValuesToString(som.freqs);
			insertNnIntoDb(subscriber_id, som_values_as_text.toString(),
					trainData.size(), ilvs, ihvs, ofvs);
		}
	}

	private String convertOutputFrequencyValuesToString(int[] freqs) {
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < freqs.length; i++) {
			sb.append(String.format("%d,", freqs[i]));
		}
		sb.deleteCharAt(sb.length() - 1);
		return sb.toString();
	}

	private String computeLowerValues(List<FeedRecord> trainData) {
		int R = 9;
		StringBuilder ilvs = new StringBuilder();
		double[] min = new double[R];
		for (int i = 0; i < R; i++)
			min[i] = Double.MAX_VALUE;
		for (FeedRecord fr : trainData) {
			if (fr.quarter < min[0]) {
				min[0] = fr.quarter;
			}
			if (fr.nbr_calls < min[1]) {
				min[1] = fr.nbr_calls;
			}
			if (fr.on_weekend < min[2]) {
				min[2] = fr.on_weekend;
			}
			if (fr.avg_call_duration < min[3]) {
				min[3] = fr.avg_call_duration;
			}
			if (fr.perc_free < min[4]) {
				min[4] = fr.perc_free;
			}
			if (fr.perc_fixed < min[5]) {
				min[5] = fr.perc_fixed;
			}
			if (fr.perc_international < min[6]) {
				min[6] = fr.perc_international;
			}
			if (fr.perc_premium < min[7]) {
				min[7] = fr.perc_premium;
			}
			if (fr.perc_hidden < min[8]) {
				min[8] = fr.perc_hidden;
			}
		}
		for (int i = 0; i < min.length; i++) {
			ilvs.append(min[i]);
			if (i < min.length - 1)
				ilvs.append(";");
		}
		return ilvs.toString();
	}

	private String computeHigherValues(List<FeedRecord> trainData) {
		int R = 9;
		StringBuilder ilvs = new StringBuilder();
		double[] max = new double[R];
		for (int i = 0; i < R; i++)
			max[i] = -Double.MAX_VALUE;
		for (FeedRecord fr : trainData) {
			if (fr.quarter > max[0]) {
				max[0] = fr.quarter;
			}
			if (fr.nbr_calls > max[1]) {
				max[1] = fr.nbr_calls;
			}
			if (fr.on_weekend > max[2]) {
				max[2] = fr.on_weekend;
			}
			if (fr.avg_call_duration > max[3]) {
				max[3] = fr.avg_call_duration;
			}
			if (fr.perc_free > max[4]) {
				max[4] = fr.perc_free;
			}
			if (fr.perc_fixed > max[5]) {
				max[5] = fr.perc_fixed;
			}
			if (fr.perc_international > max[6]) {
				max[6] = fr.perc_international;
			}
			if (fr.perc_premium > max[7]) {
				max[7] = fr.perc_premium;
			}
			if (fr.perc_hidden > max[8]) {
				max[8] = fr.perc_hidden;
			}
		}
		for (int i = 0; i < max.length; i++) {
			ilvs.append(max[i]);
			if (i < max.length - 1)
				ilvs.append(";");
		}
		return ilvs.toString();
	}

	private boolean loadNNFromDb(String subscriber_id) {
		boolean found = false;
		try {

			pstmtLoadNN.setString(1, subscriber_id);
			resultSet = pstmtLoadNN.executeQuery();
			found = resultSet.next();
			if (found) {
				numberOfRecordsUsed = resultSet.getInt(1);
				eg_contents = resultSet.getString(2);
				input_low_values = resultSet.getString(3);
				input_high_values = resultSet.getString(4);
				frequencies_values_s = resultSet.getString(5);
			}
		} catch (Exception e) {
			e.printStackTrace();
			close();
		} finally {

		}
		return found;
	}

	private void insertNnIntoDb(String subscriber_id,
			String som_values_as_text, int train_records_no, String ilvs,
			String ihvs, String fvs) {
		System.out.println("Inserting NN for subscriber " + subscriber_id);
		try {
			pstmtInsNN.setString(1, subscriber_id);
			pstmtInsNN.setString(2, som_values_as_text);
			pstmtInsNN.setInt(3, train_records_no);
			pstmtInsNN.setString(4, ilvs);
			pstmtInsNN.setString(5, ihvs);
			pstmtInsNN.setString(6, fvs);
			boolean f = pstmtInsNN.execute();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		} finally {

		}
	}

	private void updateNnIntoDb(String subscriber_id,
			String som_values_as_text, int train_records_no) {
		System.out.println("Updating NN for subscriber " + subscriber_id);
		try {
			pstmtUpdateNN.setString(1, som_values_as_text);
			pstmtUpdateNN.setInt(2, train_records_no);
			pstmtUpdateNN.setString(3, subscriber_id);
			boolean f = pstmtUpdateNN.execute();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		} finally {

		}
	}

	public List<String> getSubscribersWithCallData() {
		List<String> subscribers = new ArrayList<String>();
		try {
			pstmtGetSubscribers.setTimestamp(1,
					new Timestamp(start_time.getMillis()));
			pstmtGetSubscribers.setTimestamp(2,
					new Timestamp(end_time.getMillis()));
			resultSet = pstmtGetSubscribers.executeQuery();
			while (resultSet.next()) {
				String user_id = resultSet.getString("user_id");
				subscribers.add(user_id);
				System.out.printf("User %s calls=%d\n", user_id,
						resultSet.getInt("callsno"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			close();
		} finally {

		}
		return subscribers;
	}

	public List<String> getSubscribersWithCallData(int calls_lower_limit) {
		List<String> subscribers = new ArrayList<String>();
		try {

			pstmtGetSubscribersLimit.setTimestamp(1,
					new Timestamp(start_time.getMillis()));
			pstmtGetSubscribersLimit.setTimestamp(2,
					new Timestamp(end_time.getMillis()));
			pstmtGetSubscribersLimit.setString(3, SME);
			pstmtGetSubscribersLimit.setInt(4, calls_lower_limit);
			resultSet = pstmtGetSubscribersLimit.executeQuery();
			System.out.printf(
					"Subscribers with a minimum of %d calls in the period\n",
					systemConfiguration.getTrainingParameters()
							.getMinimumNumberOfCalls());
			while (resultSet.next()) {
				String user_id = resultSet.getString("src_id");
				int callsno = resultSet.getInt("callsno");
				subscribers.add(String.format("%s", user_id));
				System.out.printf("User %s calls=%d\n", user_id, callsno);
			}
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
		return subscribers;
	}

	private void close() {
		try {
			// if (resultSet != null) {
			// resultSet.close();
			// }
			// if (pstmt != null) {
			// pstmt.close();
			// }
			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
